package com.leo.oplayer.dao;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.leo.oplayer.model.ConDetail;
import com.leo.oplayer.model.MonthStat;
import com.leo.oplayer.model.WhitePerson;
import com.leo.oplayer.util.Constancts;

public class DBService {
	
	private static final String TAG = "DBService";
	
	private SQLiteOpenHelper mOpenHelper;
	
	private static final String[] TOTAL_PROJECTION = {MonthStat._PERSON_ID,
		"sum(in_call_num) as in_call_num",
		"sum(out_call_num) as out_call_num",
		"sum(in_msg_num) as in_msg_num",
		"sum(out_msg_num) as out_msg_num",
		"sum(point) as point",
		"remark",};	

	
	private static final String[] MAX_MIN_PROJECTION = {"min(point) as min_point",};
	
	public DBService(){}
	
	public DBService(Context context){
		mOpenHelper = new DatabaseHelper(context);
	}
	
	/**
	 * 查询统计值 月份/手机号码
	 * @param month
	 * @param phoneId
	 * @return
	 */
	public MonthStat selectStatById(String month,String phoneId){
		SQLiteDatabase db = mOpenHelper.getReadableDatabase();
		String selection = MonthStat._PERSON_ID +" = '"+phoneId+"' and "+MonthStat._IN_MONTH+" = '"+month+"'";
		Cursor cursor = null;
		try{
			cursor = db.query(MonthStat.TABLE_NAME, null, selection, null, null, null, null);
			if(cursor.moveToFirst()){
				MonthStat stat = new MonthStat(cursor);				
				return stat;
			}
		}catch(Exception e){
			Log.e(TAG, "select mont_stat error");
			return null;
		}finally{
			if(cursor != null && !cursor.isClosed()){
				cursor.close();
			}
			if(db !=null && db.isOpen()){
				db.close();
			}
		}
		return null;
	}	
	
	/**
	 * 查询统计列表-按月
	 * @param month
	 * @return
	 */
	public List<MonthStat> searchStatList(String month){
		SQLiteDatabase db = mOpenHelper.getReadableDatabase();
		String selection = ("".equals(month) || month==null) ? null:MonthStat._IN_MONTH+" = '"+month+"'";
		String orderBy = "point desc";
		Cursor cursor = null;
		List<MonthStat> list = new ArrayList<MonthStat>();
		try{
			cursor = db.query(MonthStat.TABLE_NAME, null, selection, null, null, null, orderBy);	
			if(cursor.moveToFirst()){
				while(!cursor.isAfterLast()){
					MonthStat stat = new MonthStat(cursor);		
					list.add(stat);
					cursor.moveToNext();
				}
			}
		}catch(Exception e){
			Log.e(TAG, "select month_stat list error");
			return list;
		}finally{
			if(cursor != null && !cursor.isClosed()){
				cursor.close();
			}
			if(db !=null && db.isOpen()){
				db.close();
			}
		}
		return list;
	}
	
	/**
	 * 按月查询取最大最小值
	 * @param month
	 * @return
	 */
	public Integer getMonthMin(String month){
		SQLiteDatabase db = mOpenHelper.getReadableDatabase();
		String selection = ("".equals(month) || month==null) ? null:MonthStat._IN_MONTH+" = '"+month+"'";		
		Cursor cursor = null;
		Integer min = 0;
		try{
			cursor = db.query(MonthStat.TABLE_NAME, MAX_MIN_PROJECTION, selection, null, MonthStat._PERSON_ID, null, null);	
			if(cursor!=null && cursor.moveToFirst()){
				min = cursor.getInt(0);
			}
		}catch(Exception e){
			Log.e(TAG, "select month max min  error");
			return 0;
		}finally{
			if(cursor != null && !cursor.isClosed()){
				cursor.close();
			}
			if(db !=null && db.isOpen()){
				db.close();
			}
		}
		return min;
	}
	
	/**
	 * 查询统计列表总值
	 * @param month
	 * @return
	 */
	public List<MonthStat> searchTotalList(){
		SQLiteDatabase db = mOpenHelper.getReadableDatabase();		
		String orderBy = "point desc";
		Cursor cursor = null;
		List<MonthStat> list = new ArrayList<MonthStat>();
		try{
			cursor = db.query(MonthStat.TABLE_NAME, TOTAL_PROJECTION, null, null, MonthStat._PERSON_ID, null, orderBy);	
			if(cursor.moveToFirst()){
				while(!cursor.isAfterLast()){
					MonthStat stat = new MonthStat();	
					stat.setPhoneId(cursor.getString(0));
					stat.setInCallNum(cursor.getInt(1));
					stat.setOutCallNum(cursor.getInt(2));
					stat.setInMsgNum(cursor.getInt(3));
					stat.setOutMsgNum(cursor.getInt(4));
					stat.setPoint(cursor.getInt(5));
					stat.setRemark(cursor.getString(6));
					list.add(stat);
					cursor.moveToNext();
				}
			}
		}catch(Exception e){
			Log.e(TAG, "select month_stat list error");
			return list;
		}finally{
			if(cursor != null && !cursor.isClosed()){
				cursor.close();
			}
			if(db !=null && db.isOpen()){
				db.close();
			}
		}
		return list;
	}
	
	/**
	 * 查询总列表的最小分值
	 * @param month
	 * @return
	 */
	public Integer getTotalMin(){
		SQLiteDatabase db = mOpenHelper.getReadableDatabase();		
		String orderBy = "point asc";
		Cursor cursor = null;
		Integer min = 0;
		try{
			cursor = db.query(MonthStat.TABLE_NAME, TOTAL_PROJECTION, null, null, MonthStat._PERSON_ID, null, orderBy);	
			if(cursor.moveToFirst()){
				min = cursor.getInt(cursor.getColumnIndex(MonthStat._POINT));
			}
		}catch(Exception e){
			Log.e(TAG, "select month_stat list error");
			return 0;
		}finally{
			if(cursor != null && !cursor.isClosed()){
				cursor.close();
			}
			if(db !=null && db.isOpen()){
				db.close();
			}
		}
		return min;
	}
	
	
	/**
	 * 增加统计数据
	 * @param stat
	 * @return
	 */
	public long insertMonthStat(MonthStat stat){
		SQLiteDatabase db = mOpenHelper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(MonthStat._PERSON_ID, stat.getPhoneId());
		values.put(MonthStat._IN_MONTH, stat.getMonth());
		values.put(MonthStat._IN_CALL_NUM, stat.getInCallNum());
		values.put(MonthStat._IN_MSG_NUM, stat.getInMsgNum());
		values.put(MonthStat._OUT_CALL_NUM, stat.getOutCallNum());
		values.put(MonthStat._OUT_MSG_NUM, stat.getOutMsgNum());
		values.put(MonthStat._POINT, stat.getPoint());
		values.put(MonthStat._REMARK, stat.getRemark());
		return db.insert(MonthStat.TABLE_NAME, MonthStat._ID, values);
	}
	
	/**
	 * 更新统计数值
	 * <br> 例如：update con_month_stat set in_call_num = in_call_num+1,point=point+10 where month='201001' and person_id='13800138000'
	 * @param stat
	 * @param column
	 */
	public void updateMonthStat(MonthStat stat,String column){
		SQLiteDatabase db = mOpenHelper.getWritableDatabase();
		String set = column + "=" + column + "+1";
		if(MonthStat._IN_CALL_NUM.equals(column) || MonthStat._OUT_CALL_NUM.equals(column)){
			set = set + ",point = point+"+Constancts.POINT_TELE;
		}else if(MonthStat._IN_MSG_NUM.equals(column) || MonthStat._OUT_MSG_NUM.equals(column)){
			set = set +",point = point+"+Constancts.POINT_SMS;
		}
		String sql = "update con_month_stat set "+set+" where in_month='"+stat.getMonth()+"' and "+MonthStat._PERSON_ID+"='"+stat.getPhoneId()+"';";
	
		db.execSQL(sql);
	}
	
	//----------------------通信详情-----------------------------------------------
	
	/**
	 * 插入通讯详情数据
	 * @param detail
	 * @return
	 */
	public long insertDetail(ConDetail detail){
		SQLiteDatabase db = mOpenHelper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(ConDetail._PHONEID, detail.getPhoneId());	
		values.put(ConDetail._FLAG, detail.getFlag());	
		values.put(ConDetail._CON_START_TIME, detail.getConStartTime());	
		values.put(ConDetail._CON_END_TIME, detail.getConEndTime());	
		values.put(ConDetail._DURATE_TIME, detail.getDurateTime());	
		values.put(ConDetail._REMARK, detail.getRemark());	
		return db.insert(ConDetail.TABLE_NAME, MonthStat._ID, values);
	}
	
	/**
	 * 查询通信详情列表
	 * @param detail
	 * @return
	 */
	public List<ConDetail> searchDetailList(ConDetail detail){
		SQLiteDatabase db = mOpenHelper.getReadableDatabase();
		String selection = ConDetail._PHONEID+" = '"+detail.getPhoneId()+"'";
		String orderBy = ConDetail._CON_START_TIME + " desc ";
		Cursor cursor = null;
		List<ConDetail> list = new ArrayList<ConDetail>();
		try{
			cursor = db.query(ConDetail.TABLE_NAME, null, selection, null, null, null, orderBy);	
			if(cursor.moveToFirst()){
				while(!cursor.isAfterLast()){
					ConDetail temp = new ConDetail(cursor);		
					list.add(temp);
					cursor.moveToNext();
				}
			}
		}catch(Exception e){
			Log.e(TAG, "select commulicate detail list error");
			return list;
		}finally{
			if(cursor != null && !cursor.isClosed()){
				cursor.close();
			}
			if(db !=null && db.isOpen()){
				db.close();
			}
		}
		return list;
	}
	
	/**
	 * 查询通信详情列表
	 * @param detail
	 * @return
	 */
	public String getLatestTime(String personId){
		SQLiteDatabase db = mOpenHelper.getReadableDatabase();
		String selection = ConDetail._PHONEID+" = '" + personId + "'";
		Cursor cursor = null;
		String maxTime = null;	
		String orderBy = ConDetail._CON_START_TIME + " desc ";
		try{
			cursor = db.query(ConDetail.TABLE_NAME, null, selection, null, null, null, orderBy);	
			if(cursor != null && cursor.moveToFirst()){				
				maxTime = cursor.getString(cursor.getColumnIndex(ConDetail._CON_START_TIME));				
			}
		}catch(Exception e){
			Log.e(TAG, "select max contact time error");
			return null;
		}finally{
			if(cursor != null && !cursor.isClosed()){
				cursor.close();
			}
			if(db !=null && db.isOpen()){
				db.close();
			}
		}
		return maxTime;
	}
	
	//----------------------白名单的操作---------------------------------------------------

	/**
	 * 查询白名单
	 * @return
	 */
	public List<WhitePerson> searchWhiteList(String personId){
		SQLiteDatabase db = mOpenHelper.getReadableDatabase();
		Cursor cursor = null;
		String selection = null;
		if(personId != null && !"".equals(personId)){
			selection = WhitePerson._PERSON_ID+" = '" + personId + "'";
		}
		List<WhitePerson> list = new ArrayList<WhitePerson>();
		try{
			cursor = db.query(WhitePerson.TABLE_NAME, null, selection, null, null, null, null);
			if(cursor.moveToFirst()){
				while(!cursor.isAfterLast()){
					WhitePerson temp = new WhitePerson(cursor);		
					list.add(temp);
					cursor.moveToNext();
				}
			}
		}catch(Exception e){
			Log.e(TAG, "select white person list error");
			return list;
		}finally{
			if(cursor != null && !cursor.isClosed()){
				cursor.close();
			}
			if(db !=null && db.isOpen()){
				db.close();
			}
		}
		return list;
	}
	
	/**
	 * 增加白名单
	 * @param personId
	 * @return
	 */
	public long insertWhite(String personId){
		//查询白名单中是否存在该person，没有则添加
		List<WhitePerson> list = searchWhiteList(personId);
		if(list == null || list.isEmpty()){
			SQLiteDatabase db = mOpenHelper.getWritableDatabase();
			ContentValues values = new ContentValues();
			values.put(WhitePerson._PERSON_ID, personId);
			return db.insert(WhitePerson.TABLE_NAME, null, values);
		}else{
			return 0;
		}
	}
	
	/**
	 * 删除白名单
	 * @param personId
	 * @return
	 */
	public int deleteWhite(String personId){
		SQLiteDatabase db = mOpenHelper.getWritableDatabase();
		String where = WhitePerson._PERSON_ID+" = '"+ personId +"'";
		return db.delete(WhitePerson.TABLE_NAME, where, null);
	}
	
	private static class DatabaseHelper extends SQLiteOpenHelper{
		private static final String TAG = "DatabaseHelper";
		private static final String DATABASE_NAME = "contact_stat.db";
		private static final int DATABASE_VERSION = 1;
		
		public DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);			
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			Log.v(TAG, "db onCreate begin------------------");
			
			StringBuffer sql = new StringBuffer();
			//月统计表
			sql.append("CREATE TABLE if not exists con_month_stat(");
			sql.append("_id INTEGER PRIMARY KEY AUTOINCREMENT,");
			sql.append("person_id varchar(30),");
			sql.append("in_month varchar(8),");
			sql.append("in_call_num INTEGER,");
			sql.append("out_call_num INTEGER,");
			sql.append("in_msg_num INTEGER,");
			sql.append("out_msg_num INTEGER,");
			sql.append("point INTEGER,");
			sql.append("remark varchar(30))");
			db.execSQL(sql.toString());
			
			//通讯详情表
			sql = new StringBuffer();
			sql.append("CREATE TABLE if not exists con_detail(");
			sql.append("_id INTEGER PRIMARY KEY AUTOINCREMENT,");
			sql.append("person_id varchar(30),");
			sql.append("flag INTEGER,");
			sql.append("con_start_time varchar(14),");
			sql.append("con_end_time varchar(14),");
			sql.append("durate_time INTEGER,");
			sql.append("remark varchar(30))");
			db.execSQL(sql.toString());
			
			//白名单
			sql = new StringBuffer();
			sql.append("CREATE TABLE if not exists con_white_list(");
			sql.append("_id INTEGER PRIMARY KEY AUTOINCREMENT,");
			sql.append("person_id varchar(30),");
			sql.append("remark varchar(30))");
			db.execSQL(sql.toString());
			
			//测试数据
//			String insql = "insert into con_month_stat(in_phone_number,in_month,in_call_num,out_call_num,in_msg_num,out_msg_num,point,remark) values";
//			insql = insql + "('13800138000','201001',1,2,3,4,32,'显示中文');";
//			db.execSQL(insql);
			
			Log.v(TAG, "DB init success!------------------------");
			
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			
		}
		
	}

}
